using OrderSystem.Common;
using OrderSystem.SystemFramework;
using System;
using System.Data;
using System.Data.SqlClient;

namespace OrderSystem.DataAccess
{
    public class FlowcardItems : IDisposable
    {
        private SqlDataAdapter dataAdapter;

        private SqlCommand selectCommand;

        private SqlCommand insertCommand;

        private SqlCommand updateCommand;

        private SqlCommand deleteCommand;

        public DataTable GetItems(long nFlowcardID, Sequence eSequence)
        {
            DataTable dataTable = new DataTable("FlowcardItem");
            string text = "SELECT p.PKID AS 产品ID,pt.[Name] AS 类型,pt.Unit AS 单位,p.ProductCode AS 编号,p.Width AS 宽,p.SubtenseOfWidth as '宽(短边)',p.Height AS 高,p.SubtenseOfHeight as '高(短边)',fi.Quantity AS 数量,dbo.ItemSquare(p.Width,p.Height,fi.Quantity) AS 面积,p.Price AS 价格,p.Remark AS 备注,dbo.GetDetailedTechnicsOfProductID(p.PKID) as 工艺 FROM FlowcardItem AS fi inner join Product as p on fi.ProductID=p.PKID INNER JOIN ProductType AS pt ON p.ProductTypeID=pt.PKID WHERE fi.FlowcardID=@FlowcardID";
            switch (eSequence)
            {
                case Sequence.ByPKID:
                    text += " ORDER BY pt.[Sequence], CONVERT( INT, substring(p.PKID,len(p.PKID)-charindex('-',reverse(p.PKID))+2, charindex('-',reverse(p.PKID))-1) )";
                    break;
                case Sequence.BySize:
                    text += " ORDER BY pt.[Sequence],p.Width DESC,p.Height DESC";
                    break;
            }
            try
            {
                this.selectCommand = new SqlCommand(text, new SqlConnection(AppSettings.ConnectionString));
                this.selectCommand.Connection.Open();
                this.selectCommand.Parameters.Add(new SqlParameter("@FlowcardID", SqlDbType.BigInt));
                this.selectCommand.Parameters["@FlowcardID"].Value = nFlowcardID;
                this.dataAdapter = new SqlDataAdapter(this.selectCommand);
                this.dataAdapter.Fill(dataTable);
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            return dataTable;
        }

        public DataSet GetItemsForFlowcard(long nFlowcardID, Sequence eSequence)
        {
            DataSet dataSet = new DataSet("Item");
            string str = "SELECT p.PKID AS 产品ID,pt.[Name] AS 产品类型,pt.Unit AS 类型单位,p.ProductCode AS 产品编号,p.Width AS 宽,p.SubtenseOfWidth as '宽(短边)',p.Height AS 高,p.SubtenseOfHeight as '高(短边)',fi.Quantity AS 产品数量,p.Price AS 产品价格,p.Remark AS 产品备注,dbo.GetDetailedTechnicsOfProductID(p.PKID) as 产品工艺 FROM FlowcardItem as fi inner join Product AS p on fi.ProductID=p.PKID INNER JOIN ProductType AS pt ON p.ProductTypeID=pt.PKID WHERE fi.FlowcardID=@FlowcardID";
            string str2 = " SELECT ts.technicsName AS 工艺,sum(convert(decimal(12, 4),ts.quantity)*fi.Quantity) AS 数量,ts.unitPrice as 单价 FROM Technics as ts inner join Product AS p on ts.ProductID=p.PKID inner join FlowcardItem as fi on fi.ProductID=p.PKID WHERE fi.FlowcardID=@FlowcardID group by ts.technicsName,ts.unitPrice order by ts.technicsName";
            switch (eSequence)
            {
                case Sequence.ByPKID:
                    str += " ORDER BY pt.[Sequence], CONVERT( INT, substring(p.PKID,len(p.PKID)-charindex('-',reverse(p.PKID))+2, charindex('-',reverse(p.PKID))-1) )";
                    break;
                case Sequence.BySize:
                    str += " ORDER BY pt.[Sequence],p.Width DESC,p.Height DESC";
                    break;
            }
            try
            {
                this.selectCommand = new SqlCommand(str + str2, new SqlConnection(AppSettings.ConnectionString));
                this.selectCommand.Connection.Open();
                this.selectCommand.Parameters.Add(new SqlParameter("@FlowcardID", SqlDbType.BigInt));
                this.selectCommand.Parameters["@FlowcardID"].Value = nFlowcardID;
                this.dataAdapter = new SqlDataAdapter(this.selectCommand);
                this.dataAdapter.Fill(dataSet);
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            return dataSet;
        }

        public DataTable GetDataForLabel(long nFlowcardID, Sequence eSequence)
        {
            DataTable dataTable = new DataTable();
            string text = "SELECT o.LabelName as 标签名称,c.CompanyName as 公司名称,pj.ProjectName as 计划名称,p.PKID as 产品ID,pt.[Name] as 产品类型,pt.Unit as 类型单位,p.ProductCode as 产品编号,p.Width as 宽,p.SubtenseOfWidth as '宽(短边)',p.Height as 高,p.SubtenseOfHeight as '高(短边)',fi.Quantity as 产品数量,p.Price as 产品价格,p.Remark as 产品备注,dbo.GetTechnicsOfProductID(p.PKID) as 产品工艺,pt.LabelMultiple as 标签倍数 FROM FlowcardItem as fi INNER JOIN Product AS p on fi.ProductID=p.PKID INNER JOIN ProductType AS pt ON p.ProductTypeID=pt.PKID INNER JOIN [Order] AS o ON p.OrderID=o.PKID INNER JOIN [Project] AS pj ON o.ProjectID=pj.PKID INNER JOIN Customer AS c ON pj.CustomerID=c.PKID WHERE fi.FlowcardID=@FlowcardID";
            switch (eSequence)
            {
                case Sequence.ByPKID:
                    text += " ORDER BY pt.[Sequence], CONVERT( INT, substring(p.PKID,len(p.PKID)-charindex('-',reverse(p.PKID))+2, charindex('-',reverse(p.PKID))-1) )";
                    break;
                case Sequence.BySize:
                    text += " ORDER BY pt.[Sequence],p.Width DESC,p.Height DESC";
                    break;
            }
            try
            {
                this.selectCommand = new SqlCommand(text, new SqlConnection(AppSettings.ConnectionString));
                this.selectCommand.Connection.Open();
                this.selectCommand.Parameters.Add(new SqlParameter("@FlowcardID", SqlDbType.BigInt));
                this.selectCommand.Parameters["@FlowcardID"].Value = nFlowcardID;
                this.dataAdapter = new SqlDataAdapter(this.selectCommand);
                this.dataAdapter.Fill(dataTable);
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            return dataTable;
        }

        public DataTable GetProductIDCollection(string sOrderCode)
        {
            DataTable dataTable = new DataTable("Shipping");
            string cmdText = "select p.PKID as 产品ID, p.Quantity as 产品数量, dbo.InvoiceItemQuantityOfOrderItem(p.PKID) as 发货数量 from Product as p inner join [Order] as o on p.OrderID=o.PKID where o.Code=@OrderCode";
            try
            {
                this.selectCommand = new SqlCommand(cmdText, new SqlConnection(AppSettings.ConnectionString));
                this.selectCommand.Connection.Open();
                this.selectCommand.Parameters.Add(new SqlParameter("@OrderCode", SqlDbType.VarChar, 32));
                this.selectCommand.Parameters["@OrderCode"].Value = sOrderCode;
                this.dataAdapter = new SqlDataAdapter(this.selectCommand);
                this.dataAdapter.Fill(dataTable);
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            return dataTable;
        }

        public void Dispose()
        {
            this.Dispose(true);
            GC.SuppressFinalize(true);
        }

        protected virtual void Dispose(bool disposing)
        {
            if (disposing)
            {
                if (this.dataAdapter != null)
                {
                    if (this.dataAdapter.SelectCommand != null)
                    {
                        if (this.dataAdapter.SelectCommand.Connection != null)
                        {
                            this.dataAdapter.SelectCommand.Connection.Dispose();
                            this.dataAdapter.SelectCommand.Connection = null;
                        }
                        this.dataAdapter.SelectCommand.Dispose();
                        this.dataAdapter.SelectCommand = null;
                    }
                    this.dataAdapter.Dispose();
                    this.dataAdapter = null;
                }
                if (this.insertCommand != null)
                {
                    if (this.insertCommand.Connection != null)
                    {
                        this.insertCommand.Connection.Dispose();
                        this.insertCommand.Connection = null;
                    }
                    this.insertCommand.Dispose();
                    this.insertCommand = null;
                }
                if (this.updateCommand != null)
                {
                    if (this.updateCommand.Connection != null)
                    {
                        this.updateCommand.Connection.Dispose();
                        this.updateCommand.Connection = null;
                    }
                    this.updateCommand.Dispose();
                    this.updateCommand = null;
                }
                if (this.deleteCommand != null)
                {
                    if (this.deleteCommand.Connection != null)
                    {
                        this.deleteCommand.Connection.Dispose();
                        this.deleteCommand.Connection = null;
                    }
                    this.deleteCommand.Dispose();
                    this.deleteCommand = null;
                }
            }
        }
    }
}
